package collage.SUBJECT;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class SUBJECT_DAO implements ISUBJECT_DAO {

	// URL / SQL 클래스 분리시키기 
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:cjhmasterola/cjhmasterola@112.216.253.125:1521:xe";
	private String insertSQL = "insert into SUBJECT_VO values( sbj.nextval, ?, ?, ?, ?, ?, ?, sysdate )";
	private String selectSQL = "select * from SUBJECT_VO";
	private String updateSQL = "update SUBJECT_VO set sub_name = ?, sub_type_no = ?, sub_info = ?, sub_point = ?, prof_no ?, regdate=sysdate  where = sub_no ";
	private String deleteSQL = "delete SUBJECT_VO  where sub_no = ? ";
			
	@Override
	public boolean insertSubject(SUBJECT_VO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement( insertSQL );
				pstmt.setString(1, s.getSub_no() );
				pstmt.setString(2, s.getSub_name() );
				pstmt.setString(3, s.getSub_type_no() );
				pstmt.setString(4, s.getSub_info() );
				pstmt.setString(5, s.getSub_point() );
				pstmt.setString(6, s.getProf_no() );
				
			pstmt.executeUpdate();	
			result=true;
			
		} catch ( Exception ex ){
			ex.printStackTrace();
		} finally {
			closeDatabase(conn, pstmt, null);
		}
		
				
		return result;
	}
	@Override
	public List<SUBJECT_VO> selectSubject() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<SUBJECT_VO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = openDatabase();
				pstmt = conn.prepareStatement(selectSQL);
				rs = pstmt.executeQuery();
				while( rs.next() ) {
					SUBJECT_VO s = new SUBJECT_VO();
					s.setSub_tr( rs.getString( "sub_tr") ); 
					s.setSub_no( rs.getString( "sub_no") );
					s.setSub_name(rs.getString("sub_name"));
					s.setSub_type_no( rs.getString("sub_type_no"));
					s.setSub_info( rs.getString("sub_info"));
					s.setSub_point( rs.getString("sub_point"));
					s.setProf_no( rs.getString("prof_no"));
									
					result.add( s );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
			closeDatabase(conn, pstmt, rs);
			
		}
		
		return result;		
		
	}
	@Override
	public boolean updateSubject(SUBJECT_VO s) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
				pstmt.setString(1, s.getSub_name() );
				pstmt.setString(2, s.getSub_type_no() );
				pstmt.setString(3, s.getSub_info() );
				pstmt.setString(4, s.getSub_point() );
				pstmt.setString(5, s.getProf_no() );
				
			int cnt = pstmt.executeUpdate();
			if ( cnt == 0 ) {
				System.out.println( "명령은 잘 실행됐지만, 수정된 데이타너는 없음" );
			}
			else {
				result = true;
			}
			
		} catch ( Exception ex ){
			ex.printStackTrace();
		} finally {
			closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public boolean deleteSubject(SUBJECT_VO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);
					pstmt.setString( 1, s.getSub_no() );
					
			int cnt = pstmt.executeUpdate();
			if ( cnt == 0 ){ 
				System.out.println( "명령은 잘 실행됐지만, 삭제된 데이타는 없음" );
				
			}
			else {
				result = true;
			}
			
		} catch ( Exception ex ) {
			ex.printStackTrace();
		} finally {
			closeDatabase(conn, pstmt, null);			
		}
		
		return result;
	}
	
	
	
	private Connection openDatabase() {
		try{	
			Class.forName(DRV);
			return DriverManager.getConnection(URL);			
		}catch( Exception ex ){
			ex.printStackTrace();
		}
		
		return  null;
	}

	
	private void closeDatabase( Connection c, PreparedStatement p, ResultSet r ){
		if ( r != null ) try { r.close(); r = null; } catch( Exception ex ) {}
		if ( p != null ) try { p.close(); p = null; } catch( Exception ex ) {}
		if ( c != null ) try { c.close(); c = null; } catch( Exception ex ) {}
		
		
	}
	
	
	public static void getSQL( ) {

	
	}
	
	
	
		
} // SUBJECT_DAO
